﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop.Excel;
using CrystalDecisions.CrystalReports.Engine;

public class TitleClass
{
    public bool Merge = false;
    public object Align = Excel.Constants.xlLeft;
    public int Col = 0;
    public int Row = 1;
    public string Text = String.Empty;
    public object Bold = false;
    public object Size = 10;
}

public struct HeaderClass
{
    public string Text;
    public string DataMember;
}

public class ReportDB
{

    static public bool exportDataToExcel(string tieude, System.Data.DataTable dt)
    {
        bool result = false;
        //khoi tao cac doi tuong Com Excel de lam viec
        Excel.ApplicationClass xlApp;
        Excel.Worksheet xlSheet;
        Excel.Workbook xlBook;
        //doi tuong Trống để thêm  vào xlApp sau đó lưu lại sau
        object missValue = System.Reflection.Missing.Value;
        //khoi tao doi tuong Com Excel moi
        xlApp = new Excel.ApplicationClass();
        xlBook = xlApp.Workbooks.Add(missValue);
        //su dung Sheet dau tien de thao tac
        xlSheet = (Excel.Worksheet)xlBook.Worksheets.get_Item(1);
        //không cho hiện ứng dụng Excel lên để tránh gây đơ máy
        xlApp.Visible = false;
        int socot = dt.Columns.Count;
        int sohang = dt.Rows.Count;
        int i, j;

        SaveFileDialog f = new SaveFileDialog();
        f.Filter = "Excel file (*.xls)|*.xls";
        if (f.ShowDialog() == DialogResult.OK)
        {


            //set thuoc tinh cho tieu de
            xlSheet.get_Range("A1", Convert.ToChar(socot + 65) + "1").Merge(false);
            Excel.Range caption = xlSheet.get_Range("A1", Convert.ToChar(socot + 65) + "1");
            caption.Select();
            caption.FormulaR1C1 = tieude;
            //căn lề cho tiêu đề
            caption.HorizontalAlignment = Excel.Constants.xlCenter;
            caption.Font.Bold = true;
            caption.VerticalAlignment = Excel.Constants.xlCenter;
            caption.Font.Size = 15;
            //màu nền cho tiêu đề
            caption.Interior.ColorIndex = 20;
            caption.RowHeight = 30;
            //set thuoc tinh cho cac header
            Excel.Range header = xlSheet.get_Range("A2", Convert.ToChar(socot + 65) + "2");
            header.Select();

            header.HorizontalAlignment = Excel.Constants.xlCenter;
            header.Font.Bold = true;
            header.Font.Size = 10;
            //điền tiêu đề cho các cột trong file excel
            for (i = 0; i < socot; i++)
                xlSheet.Cells[2, i + 2] = dt.Columns[i].ColumnName;
            //dien cot stt
            xlSheet.Cells[2, 1] = "STT";
            for (i = 0; i < sohang; i++)
                xlSheet.Cells[i + 3, 1] = i + 1;
            //dien du lieu vao sheet


            for (i = 0; i < sohang; i++)
                for (j = 0; j < socot; j++)
                {
                    xlSheet.Cells[i + 3, j + 2] = dt.Rows[i][j];

                }
            //autofit độ rộng cho các cột 
            for (i = 0; i < socot; i++)
                ((Excel.Range)xlSheet.Cells[1, i + 1]).EntireColumn.AutoFit();

            //save file
            xlBook.SaveAs(f.FileName, Excel.XlFileFormat.xlWorkbookNormal, missValue, missValue, missValue, missValue, Excel.XlSaveAsAccessMode.xlExclusive, missValue, missValue, missValue, missValue, missValue);
            xlBook.Close(true, missValue, missValue);
            xlApp.Quit();

            // release cac doi tuong COM
            releaseObject(xlSheet);
            releaseObject(xlBook);
            releaseObject(xlApp);
            result = true;
        }
        return result;
    }

    static public bool exportDataToExcel(IList<TitleClass> titles, IList<HeaderClass> headers, System.Data.DataTable dt)
    {
        // if (headers.Count != dt.Columns.Count) throw new Exception("Lỗi số cột không hợp lệ");

        bool result = false;
        try
        {
            System.Globalization.CultureInfo cultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture;
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

            //khoi tao cac doi tuong Com Excel de lam viec
            Excel.ApplicationClass xlApp;
            Excel.Worksheet xlSheet;
            Excel.Workbook xlBook;
            //doi tuong Trống để thêm  vào xlApp sau đó lưu lại sau
            object missValue = System.Reflection.Missing.Value;
            //khoi tao doi tuong Com Excel moi
            xlApp = new Excel.ApplicationClass();
            xlBook = xlApp.Workbooks.Add(missValue);
            //su dung Sheet dau tien de thao tac
            xlSheet = (Excel.Worksheet)xlBook.Worksheets.get_Item(1);
            //không cho hiện ứng dụng Excel lên để tránh gây đơ máy
            xlApp.Visible = false;
            int socot = headers.Count;
            int sohang = dt.Rows.Count;
            int i, j;

            SaveFileDialog f = new SaveFileDialog();
            f.Filter = "Excel file (*.xls)|*.xls";
            if (f.ShowDialog() == DialogResult.OK)
            {
                //set thuoc tinh cho tieu de
                foreach (var item in titles)
                {
                    if (item.Merge)
                        xlSheet.get_Range(Convert.ToChar(item.Col + 65) + item.Row.ToString(), Convert.ToChar(socot - item.Col + 65) + item.Row.ToString()).Merge(false);
                    Excel.Range caption = xlSheet.get_Range(Convert.ToChar(item.Col + 65) + item.Row.ToString(), Convert.ToChar(item.Col + 65) + item.Row.ToString());
                    caption.Select();
                    caption.FormulaR1C1 = item.Text;
                    //căn lề cho tiêu đề
                    caption.HorizontalAlignment = item.Align;
                    caption.Font.Bold = item.Bold;
                    caption.VerticalAlignment = Excel.Constants.xlCenter;
                    caption.Font.Size = item.Size;
                    //màu nền cho tiêu đề
                    //caption.Interior.ColorIndex = 20;
                    caption.RowHeight = 30;
                }
                int rowHeader = titles.Count + 1;
                int rowDetails = titles.Count + 2;

                //set thuoc tinh cho cac header
                Excel.Range header = xlSheet.get_Range("A" + rowHeader, Convert.ToChar(socot + 65).ToString() + rowHeader);
                header.Select();

                header.HorizontalAlignment = Excel.Constants.xlCenter;
                header.Font.Bold = true;
                header.Font.Size = 10;

                //điền tiêu đề cho các cột trong file excel
                for (i = 0; i < socot; i++)
                    xlSheet.Cells[rowHeader, i + 2] = headers[i].Text;
                //dien cot stt
                xlSheet.Cells[rowHeader, 1] = "STT";
                for (i = 0; i < sohang; i++)
                    xlSheet.Cells[i + rowDetails, 1] = i + 1;
                //dien du lieu vao sheet


                for (i = 0; i < sohang; i++)
                    for (j = 0; j < socot; j++)
                    {
                        if (!String.IsNullOrEmpty(headers[j].DataMember))
                            xlSheet.Cells[i + rowDetails, j + 2] = dt.Rows[i][headers[j].DataMember];
                    }
                //autofit độ rộng cho các cột 
                for (i = 0; i < socot; i++)
                    ((Excel.Range)xlSheet.Cells[1, i + 1]).EntireColumn.AutoFit();

                //save file
                xlBook.SaveAs(f.FileName, Excel.XlFileFormat.xlWorkbookNormal, missValue, missValue, false, false, Excel.XlSaveAsAccessMode.xlExclusive, false, false, false, false, false);
                //xlBook.SaveCopyAs(f.FileName);
               // xlBook.Close(true, missValue, missValue);
                xlBook.Close(false, false, false);
                xlApp.Quit();
                
                // release cac doi tuong COM

                //releaseObject(xlSheet);
                //releaseObject(xlBook);
                //releaseObject(xlApp);

                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);

                //System.Threading.Thread.CurrentThread.CurrentCulture = cultureInfo;

                result = true;
            }
        }
        catch (ExportException ex)
        {
            MessageBox.Show(ex.Message);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        return result;
    }

    static public void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
            throw new Exception("Exception Occured while releasing object " + ex.ToString());
        }
        finally
        {
            GC.Collect();
        }
    }

    static public bool exportReport(int type, ReportDocument repd)
    {
        SaveFileDialog f = new SaveFileDialog();
        bool result = false;
        switch (type)
        {
            case 1:

                f.Filter = "Word file(*.doc)|*.doc";
                if (f.ShowDialog() == DialogResult.OK)
                {
                    repd.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.WordForWindows, f.FileName);
                    result = true;
                }
                break;
            case 2:

                f.Filter = "Pdf file(*.pdf)|*.pdf";
                if (f.ShowDialog() == DialogResult.OK)
                {
                    repd.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, f.FileName);
                    result = true;
                }
                break;
            case 3:

                f.Filter = "Excel file(*.xls)|*.xls";
                if (f.ShowDialog() == DialogResult.OK)
                {
                    repd.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.Excel, f.FileName);
                    result = true;
                }
                break;
            default:
                // MessageBox.Show("Khong chon dung loai ");
                break;


        }
        return result;
    }

}
